#!/bin/bash
# Author: fanyongfeng
# 0  1 * * * /bin/bash /root/script/mysql_data_backup.sh &>> /backup/mysql_data_backup/backup.log

set -o pipefail

TODAY=$(date +%Y%m%d)
BACKUP_DIR="/backup/mysql_data_backup"
MYSQL_USER_INFO_FILE="/root/script/.mysql_user_info"
[[ ! -f ${MYSQL_USER_INFO_FILE} ]] && echo "file not exist: ${MYSQL_USER_INFO_FILE}" && exit 1
USER=$(awk '{print $1}' ${MYSQL_USER_INFO_FILE})
PASSWORD=$(awk '{print $2}' ${MYSQL_USER_INFO_FILE})

[[ -d ${BACKUP_DIR} ]] || mkdir -p ${BACKUP_DIR} 
databases=$(mysql -u${USER} -p${PASSWORD} -e "show databases;" 2>/dev/null | sed 1d | grep -Ev "schema|mysql|sys|test")

for db_name in ${databases}
do
    mkdir -p ${BACKUP_DIR}/${db_name}
    mysqldump --set-gtid-purged=OFF --single-transaction -u${USER} -p${PASSWORD} ${db_name} | gzip > ${BACKUP_DIR}/${db_name}/${db_name}_${TODAY}.sql.gz
    [[ $? -eq 0 ]] && echo "${TODAY} ${db_name}  backup success" || echo "${TODAY} ${db_name}  backup failed"   
done

find ${BACKUP_DIR} -type f -mtime +180 -name "*.sql.gz" | xargs -r -n 10 rm -f
